﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace One.Framework.Data
{
    public class SqlHelper
    {
        public static T ConvertTo<T>(object _entity)
        {
            T obj = default(T);
            if (_entity != null)
            {
                obj = Activator.CreateInstance<T>();

                System.ComponentModel.PropertyDescriptorCollection properties = System.ComponentModel.TypeDescriptor.GetProperties(_entity);

                foreach (System.ComponentModel.PropertyDescriptor prop in properties)
                {
                    System.Reflection.PropertyInfo _p = obj.GetType().GetProperty(prop.Name);
                    try
                    {
                        object value = prop.GetValue(_entity);
                        if (value.GetType().Name.Equals("DBNull")) value = null;
                        _p.SetValue(obj, value, null);
                    }
                    catch
                    {
                        // You can log something here

                    }
                }
            }

            return obj;
        }

        public string ConnectionString;
        private SqlConnection m_Connection;

        public SqlHelper()
        {
            Setup();
        }

        public SqlHelper(string Dsn)
        {
            Setup(Dsn);
        }

        public SqlHelper(string ConnectionStr, string strType)
        {
            ConnectionString = ConnectionStr;
        }

        private void Setup()
        {
            ConnectionString = CommClass.GetConfigRead("EIRBCON");
        }

        private void Setup(string Dsn)
        {
            ConnectionString = CommClass.GetConfigRead(Dsn);
        }

        #region DataReader 반환
        public SqlDataReader Get_ExcuteReader(string Query, SqlParameter[] paramArray, CommandType cmdType)
        {
            if (ConnectionString != "")
                m_Connection = new SqlConnection(ConnectionString);

            SqlDataReader dr;
            try
            {
                SqlCommand cmd = new SqlCommand(Query, m_Connection);
                cmd.CommandType = cmdType;
                m_Connection.Open();

                if (paramArray != null)
                {
                    foreach (SqlParameter param in paramArray)
                    {
                        cmd.Parameters.Add(param);
                    }
                }

                dr = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dr;
        }

        public SqlDataReader Get_ExcuteReader(string Query, CommandType cmdType)
        {
            if (ConnectionString != "")
                m_Connection = new SqlConnection(ConnectionString);

            SqlDataReader dr;
            try
            {
                SqlCommand cmd = new SqlCommand(Query, m_Connection);
                cmd.CommandType = cmdType;
                m_Connection.Open();

                dr = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dr;
        }

        public SqlDataReader Get_ExcuteReader(string Query)
        {
            return Get_ExcuteReader(Query, CommandType.Text);
        }
        #endregion

        #region Proc 호출 - DataTable 반환
        /// <summary>
        /// 엑셀용 데이터테이블 조회시
        /// </summary>
        /// <param name="Proc"></param>
        /// <param name="args"></param>
        /// <returns></returns>
        public DataTable Get_ExcuteDataTable(string Proc, Dictionary<string, object> args)
        {
            DataTable dtRtn = null;

            try
            {
                SqlParameter[] sParam = {
                    DataHelper.SqlDBParameter("@ObjectNm", SqlDbType.NVarChar, Proc)
                };

                DataTable dtParam = Get_ExcuteDataSet("USP_C_ParameterInfo", ref sParam, CommandType.StoredProcedure).Tables[0];

                if (dtParam != null && dtParam.Rows.Count > 0){
                    SqlParameter[] dParam = new SqlParameter[dtParam.Rows.Count];

                    if (args != null)
                    {
                        int index = 0;
                        SqlParameter param;

                        foreach (DataRow drParam in dtParam.Rows){
                            param = new SqlParameter();
                            param.ParameterName = drParam["Name"].ToString();
                            SetSqlDbType(param, drParam["Type"], args[param.ParameterName.Substring(1)]);

                            dParam[index] = param;
                            index++;
                        }
                    }

                    dtRtn = Get_ExcuteDataSet(Proc, ref dParam, CommandType.StoredProcedure).Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return dtRtn;
        }

        public SqlParameter SetSqlDbType(SqlParameter rtnParam, object typeNm, object value)
        {
            switch (typeNm.ToString().ToLower())
            {
                case "int": 
                    rtnParam.SqlDbType = SqlDbType.Int;
                    if (!string.IsNullOrEmpty(value.ToString()))
                        rtnParam.Value = Convert.ToInt32(value);
                    else
                        rtnParam.Value = DBNull.Value;
                    break;
                case "nvarchar": 
                    rtnParam.SqlDbType = SqlDbType.NVarChar;
                    rtnParam.Value = value.ToString();
                    break;
                case "nchar": 
                    rtnParam.SqlDbType = SqlDbType.NChar;
                    rtnParam.Value = value.ToString();
                    break;
                case "date": 
                    rtnParam.SqlDbType = SqlDbType.Date;
                    if (!string.IsNullOrEmpty(value.ToString()))
                        rtnParam.Value = Convert.ToDateTime(value);
                    else
                        rtnParam.Value = DBNull.Value;
                    break;
                case "money": 
                    rtnParam.SqlDbType = SqlDbType.Money;
                    rtnParam.Value = Convert.ToDecimal(value);
                    break;
                case "ntext": 
                    rtnParam.SqlDbType = SqlDbType.NText;
                    rtnParam.Value = value;
                    break;
                case "numeric": 
                    rtnParam.SqlDbType = SqlDbType.Decimal;
                    rtnParam.Value = Convert.ToDecimal(value);
                    break;
                case "real": 
                    rtnParam.SqlDbType = SqlDbType.Real;
                    rtnParam.Value = value;
                    break;
                case "smallint": 
                    rtnParam.SqlDbType = SqlDbType.SmallInt;
                    rtnParam.Value = Convert.ToInt16(value);
                    break;
                case "text": 
                    rtnParam.SqlDbType = SqlDbType.Text;
                    rtnParam.Value = value.ToString();
                    break;
                case "timestamp": 
                    rtnParam.SqlDbType = SqlDbType.Timestamp;
                    if (!string.IsNullOrEmpty(value.ToString()))
                        rtnParam.Value = TimeSpan.Parse(value.ToString());
                    else
                        rtnParam.Value = DBNull.Value;
                    break;
                case "time": 
                    rtnParam.SqlDbType = SqlDbType.Time;
                    if (!string.IsNullOrEmpty(value.ToString()))
                        rtnParam.Value = TimeSpan.Parse(value.ToString());
                    else
                        rtnParam.Value = DBNull.Value;
                    break;
                case "tinyint": 
                    rtnParam.SqlDbType = SqlDbType.TinyInt;
                    if (!string.IsNullOrEmpty(value.ToString()))
                        rtnParam.Value = Convert.ToInt16(value);
                    else
                        rtnParam.Value = DBNull.Value;
                    break;
                case "varchar": 
                    rtnParam.SqlDbType = SqlDbType.VarChar;
                    rtnParam.Value = value.ToString();
                    break;
                case "char": 
                    rtnParam.SqlDbType = SqlDbType.Char;
                    rtnParam.Value = value.ToString();
                    break;
                case "decimal": 
                    rtnParam.SqlDbType = SqlDbType.Decimal;
                    if (!string.IsNullOrEmpty(value.ToString()))
                        rtnParam.Value = Convert.ToDecimal(value);
                    else
                        rtnParam.Value = DBNull.Value;
                    break;
            }

            return rtnParam;
        }
        #endregion

        #region DataSet반환
        public DataSet Get_ExcuteDataSet(string Query, string sTable, DataSet dataset, ref SqlParameter[] paramArray, CommandType cmdType)
        {
            if (ConnectionString != "")
                m_Connection = new SqlConnection(ConnectionString);

            try
            {
                SqlDataAdapter adt = new SqlDataAdapter(Query, m_Connection);
                adt.SelectCommand.CommandType = cmdType;
                adt.SelectCommand.CommandTimeout = 5000;
                if (dataset == null)
                    dataset = new DataSet();

                if (paramArray != null)
                {
                    foreach (SqlParameter param in paramArray)
                    {
                        adt.SelectCommand.Parameters.Add(param);
                    }
                }

                dataset.Clear();

                adt.Fill(dataset, sTable);

                if (paramArray != null)
                {
                    foreach (SqlParameter param in paramArray)
                    {
                        if (param.Direction.Equals(ParameterDirection.Output))
                            param.Value = adt.SelectCommand.Parameters[param.ParameterName].Value;
                    }
                }

                adt.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                m_Connection.Close();
                m_Connection.Dispose();
            }

            return dataset;
        }

        public DataSet Get_ExcuteDataSet(string Query, ref SqlParameter[] paramArray, CommandType cmdType)
        {
            return Get_ExcuteDataSet(Query, "Table", null, ref paramArray, cmdType);
        }

        public DataSet Get_ExcuteDataSet(string Query)
        {
            SqlParameter[] paramArray = null;

            return Get_ExcuteDataSet(Query, "Table", null, ref paramArray, CommandType.Text);
        }

        public DataSet Get_ExcuteDataSet(string Query, string sTable)
        {
            SqlParameter[] paramArray = null;

            return Get_ExcuteDataSet(Query, sTable, null, ref paramArray, CommandType.Text);
        }
        #endregion

        #region object값 반환
        public object Get_ExcuteNonQuery(string Query, ref SqlParameter[] paramArray, CommandType cmdType)
        {
            object oValue;

            if (ConnectionString != "")
                m_Connection = new SqlConnection(ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(Query, m_Connection);
                cmd.CommandType = cmdType;

                if (paramArray != null)
                {
                    foreach (SqlParameter param in paramArray)
                    {
                        cmd.Parameters.Add(param);
                    }
                }

                m_Connection.Open();
                oValue = cmd.ExecuteScalar();

                foreach (SqlParameter param in paramArray)
                {
                    if (param.Direction.Equals(ParameterDirection.Output))
                        param.Value = cmd.Parameters[param.ParameterName].Value;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                m_Connection.Close();
                m_Connection.Dispose();
            }

            return oValue;
        }

        public object Get_ExcuteNonQuery(string Query)
        {
            SqlParameter[] paramArray = null;

            return Get_ExcuteNonQuery(Query, ref paramArray, CommandType.Text);
        }
        #endregion

        #region
        public object Get_ExcuteReturnQuery(string Query, SqlParameter[] paramArray, CommandType cmdType, SqlDbType SdType)
        {
            if (ConnectionString != "")
                m_Connection = new SqlConnection(ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(Query, m_Connection);
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = 60000;

                if (paramArray != null)
                {
                    foreach (SqlParameter param in paramArray)
                    {
                        cmd.Parameters.Add(param);
                    }
                }

                SqlParameter Param = new SqlParameter();
                Param.ParameterName = "@ReturnValue";
                Param.SqlDbType = SdType;
                Param.Direction = ParameterDirection.ReturnValue;

                cmd.Parameters.Add(Param);

                m_Connection.Open();
                cmd.ExecuteNonQuery();

                return cmd.Parameters["@ReturnValue"].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                m_Connection.Close();
                m_Connection.Dispose();
            }
        }

        public int ExcuteMultipleReturnQuery(string Query, SqlParameter[] paramArray, List<Dictionary<string, object>> paramValueSets, CommandType cmdType, SqlDbType SdType)
        {
            int returnValue = 0;

            if (ConnectionString != "")
                m_Connection = new SqlConnection(ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(Query, m_Connection);
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = 60000;

                if (paramArray != null)
                {
                    foreach (SqlParameter param in paramArray)
                    {
                        cmd.Parameters.Add(param);
                    }
                }

                SqlParameter Param = new SqlParameter();
                Param.ParameterName = "@ReturnValue";
                Param.SqlDbType = SdType;
                Param.Direction = ParameterDirection.ReturnValue;

                cmd.Parameters.Add(Param);

                m_Connection.Open();

                foreach (Dictionary<string, object> paramValueSet in paramValueSets)
                {
                    foreach (string key in paramValueSet.Keys)
                    {
                        if (cmd.Parameters.Contains(key))
                        {
                            cmd.Parameters[key].Value = paramValueSet[key];
                        }
                    }

                    cmd.ExecuteNonQuery();
                    returnValue += Convert.ToInt32(cmd.Parameters["@ReturnValue"].Value);
                }

                m_Connection.Close();

                return returnValue;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                m_Connection.Close();
                m_Connection.Dispose();
            }
        }



        public void EXE_ExcuteNonQuery(string Query, SqlParameter[] paramArray, CommandType cmdType)
        {
            if (ConnectionString != "")
                m_Connection = new SqlConnection(ConnectionString);

            try
            {
                SqlCommand cmd = new SqlCommand(Query, m_Connection);
                cmd.CommandType = cmdType;
                cmd.CommandTimeout = 60000;

                if (paramArray != null)
                {
                    foreach (SqlParameter param in paramArray)
                    {
                        cmd.Parameters.Add(param);
                    }
                }
                
                m_Connection.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                m_Connection.Close();
                m_Connection.Dispose();
            }
        }

        public void EXE_ExcuteNonQuery(string Query)
        {
            EXE_ExcuteNonQuery(Query, null, CommandType.Text);
        }
        #endregion
    }

    /*
    public class Cryptography
    {
        #region MD5
        /// <summary>
        /// MD5 변환
        /// </summary>
        /// <param name="input">원문</param>
        /// <returns>코드</returns>
        public static string getMd5Hash(string input)
        {
            System.Security.Cryptography.MD5 md5Hasher = System.Security.Cryptography.MD5.Create();

            byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(input));

            StringBuilder sBuilder = new StringBuilder();

            for (int i = 0; i < data.Length; i++)
                sBuilder.Append(data[i].ToString("x2"));

            return sBuilder.ToString();
        }

        /// <summary>
        ///  MD5 비교
        /// </summary>
        /// <param name="input">원문</param>
        /// <param name="hash">코드</param>
        /// <returns>결과 값</returns>
        public static bool verifyMd5Hash(string input, string hash)
        {
            string hashOfInput = getMd5Hash(input);

            StringComparer comparer = StringComparer.OrdinalIgnoreCase;

            if (0 == comparer.Compare(hashOfInput, hash))
                return true;
            else
                return false;
        }
        #endregion
    }
     * */

    public class CommClass
    {
        #region AppSettings반환
        public static string GetAppRead(string str)
        {
            return System.Configuration.ConfigurationManager.AppSettings[str];
        }

        public static string GetConfigRead()
        {
            return GetConfigRead("EIRBCON");
        }

        public static string GetConfigRead(string str)
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings[str].ConnectionString;
        }
        #endregion
    }

    public class DataHelper
    {
        public static string Get_ExcuteNonQuery(string Query)
        {
            string ReturnString;

            try
            {
                SqlHelper dx = new SqlHelper();
                ReturnString = dx.Get_ExcuteNonQuery(Query).ToString();
            }
            catch
            {
                ReturnString = string.Empty;
            }

            return ReturnString;
        }

        public static string Get_ExcuteNonQuery(string Query, string Dsn)
        {
            string ReturnString;

            try
            {
                SqlHelper dx = new SqlHelper(Dsn);
                ReturnString = dx.Get_ExcuteNonQuery(Query).ToString();
            }
            catch
            {
                ReturnString = string.Empty;
            }

            return ReturnString;
        }

        public static SqlDataReader Get_DataReader(string Query)
        {
            SqlHelper dx = new SqlHelper();
            SqlDataReader dr = dx.Get_ExcuteReader(Query);

            return dr;
        }

        public static void EXE_ExcruteNonQuery(string Query)
        {
            SqlHelper dx = new SqlHelper();
            dx.EXE_ExcuteNonQuery(Query);
        }

        public static void EXE_ExcruteNonQuery(string Query, string DSN)
        {
            SqlHelper dx = new SqlHelper();
            dx.ConnectionString = CommClass.GetConfigRead(DSN);

            dx.EXE_ExcuteNonQuery(Query);
        }

        #region SqlParameter
        public static SqlParameter SqlDBParameter(string ParameterName, SqlDbType St, object oValue)
        {
            SqlParameter Sp = new SqlParameter(ParameterName, St);
            Sp.Value = oValue;

            return Sp;
        }

        public static SqlParameter SqlDBParameter(string ParameterName, SqlDbType St)
        {
            SqlParameter Sp = new SqlParameter(ParameterName, St);
            return Sp;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ParameterName"></param>
        /// <param name="St"></param>
        /// <param name="iSize"></param>
        /// <param name="oValue"></param>
        /// <param name="bNull">참일 경우 DBNull 값 적용</param>
        /// <returns></returns>
        public static SqlParameter SqlDBParameter(string ParameterName, SqlDbType St, object oValue, bool bNull)
        {
            SqlParameter Sp = new SqlParameter(ParameterName, St);

            if (bNull)
                Sp.Value = DBNull.Value;
            else
                Sp.Value = oValue;

            return Sp;
        }

        public static SqlParameter SqlDBParameter(string ParameterName, SqlDbType St, object oValue, ParameterDirection Pd)
        {
            SqlParameter Sp = new SqlParameter(ParameterName, St);
            Sp.Value = oValue;
            Sp.Direction = Pd;

            return Sp;
        }

        public static SqlParameter SqlDBParameter(string ParameterName, SqlDbType St, object oValue, int Size, ParameterDirection Pd)
        {
            SqlParameter Sp = new SqlParameter(ParameterName, St);
            Sp.Value = oValue;
            Sp.Direction = Pd;
            Sp.Size = Size;

            return Sp;
        }
        #endregion

        #region 단순 쿼리 반환
        public static DataSet Get_QueryResult(string sField, string sTable, string sWhere, string sOrder)
        {
            int iRecodeCount = -1;

            return Get_QueryResult(sField, sTable, false, sWhere, sOrder, null, out iRecodeCount);
        }

        public static DataSet Get_QueryResult(string sField, string sTable, bool GetRecodeCount, string sFilter, string sSort, string sGroup, out int iRecodeCount)
        {
            SqlParameter[] oParams = new SqlParameter[6];
            oParams[0] = new SqlParameter("@strFields", SqlDbType.VarChar, 4000);
            oParams[0].Value = sField;
            oParams[1] = new SqlParameter("@strTables", SqlDbType.VarChar, 4000);
            oParams[1].Value = sTable;
            oParams[2] = new SqlParameter("@blnGetRecordCount", SqlDbType.Int, 1);
            oParams[2].Value = GetRecodeCount;
            oParams[3] = new SqlParameter("@strFilter", SqlDbType.VarChar, 8000);
            oParams[3].Value = sFilter;
            oParams[4] = new SqlParameter("@strSort", SqlDbType.VarChar, 8000);
            oParams[4].Value = sSort;
            oParams[5] = new SqlParameter("@strGroup", SqlDbType.VarChar, 8000);
            oParams[5].Value = sGroup;

            iRecodeCount = -1;

            SqlHelper dx = new SqlHelper();
            DataSet ds = dx.Get_ExcuteDataSet("usp_ReturnQuery", sTable, null, ref oParams, CommandType.StoredProcedure);

            if (ds.Tables.Count != 0)
            {
                if (ds.Tables.Count == 2 && ds.Tables[1].Rows.Count != 0)
                    iRecodeCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            }

            return ds;
        }
        #endregion

        #region 페이징 리피터 바인딩

        public static DataSet Get_PagingList(int iPage, string sField, string sPK,
            string sTable, int iPageSize, bool GetRecodeCount, string sFilter, string sSort, string sGroup, out int iRecodeCount)
        {
            SqlParameter[] oParams = new SqlParameter[9];
            oParams[0] = new SqlParameter("@strFields", SqlDbType.VarChar, 4000);
            oParams[0].Value = sField;
            oParams[1] = new SqlParameter("@strPK", SqlDbType.VarChar, 100);
            oParams[1].Value = sPK;
            oParams[2] = new SqlParameter("@strTables", SqlDbType.VarChar, 4000);
            oParams[2].Value = sTable;
            oParams[3] = new SqlParameter("@intPageNo", SqlDbType.Int, 38);
            oParams[3].Value = iPage;
            oParams[4] = new SqlParameter("@intPageSize", SqlDbType.Int, 38);
            oParams[4].Value = iPageSize;
            oParams[5] = new SqlParameter("@blnGetRecordCount", SqlDbType.Int, 1);
            oParams[5].Value = GetRecodeCount;
            oParams[6] = new SqlParameter("@strFilter", SqlDbType.VarChar, 8000);
            oParams[6].Value = sFilter;
            oParams[7] = new SqlParameter("@strSort", SqlDbType.VarChar, 8000);
            oParams[7].Value = sSort;
            oParams[8] = new SqlParameter("@strGroup", SqlDbType.VarChar, 8000);
            oParams[8].Value = sGroup;

            iRecodeCount = -1;

            SqlHelper dx = new SqlHelper();
            DataSet ds = dx.Get_ExcuteDataSet("Usp_PagingQuery", sTable, null, ref oParams, CommandType.StoredProcedure);

            if (ds.Tables.Count != 0)
            {
                if (ds.Tables.Count == 2 && ds.Tables[1].Rows.Count != 0)
                    iRecodeCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            }

            return ds;
        }
        #endregion
    }
}
